package com.crm.daoImpl;

import com.crm.dao.CustomerDao;
import com.crm.pojo.Customer;
import com.crm.pojo.CustomerDetail;
import com.crm.util.jdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @version : 1.0
 * @auther : hjx
 * @Date : 2021/6/14
 * @Description : 实训
 */
public class CustomerDaoImpl implements CustomerDao {

    @Override
    public Boolean addCustomer(String custName, String custSource, String custIndustry,
                               String custLevel, String custPhone, String custMobile,
                               String custRegion, long custCapital, String custAddress,
                               String custZip, String custFax, String custWebsite,
                               String custLicence, String custPic, String custCorporation,
                               String custBank) {

        Connection conn = null;
        String sql1 = "insert into cst_customer(cust_name,cust_source,cust_industry,cust_level,cust_phone,cust_mobile) values(?,?,?,?,?,?)";
        String sql2 = "insert into cst_customer_detail(cust_region,cust_capital,cust_address,cust_zip,cust_fax,cust_website,cust_licence,cust_pic,cust_corporation,cust_bank) values(?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        int rs = 0;
        int rs2 = 0;

        try {
            conn = jdbcUtils.getCon();
            ps =conn.prepareStatement(sql1);
            ps.setString(1, custName);
            ps.setString(2, custSource);
            ps.setString(3, custIndustry);
            ps.setString(4, custLevel);
            ps.setString(5, custPhone);
            ps.setString(6, custMobile);
            rs=ps.executeUpdate();

            ps2 =conn.prepareStatement(sql2);
            ps2.setString(1, custRegion);
            ps2.setLong(2, custCapital);
            ps2.setString(3, custAddress);
            ps2.setString(4, custZip);
            ps2.setString(5, custFax);
            ps2.setString(6, custWebsite);
            ps2.setString(7, custLicence);
            ps2.setString(8, custPic);
            ps2.setString(9, custCorporation);
            ps2.setString(10, custBank);
            rs2=ps2.executeUpdate();

            if (rs == 1 && rs2 == 1) {
                return true;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally{
            jdbcUtils.close(conn, ps, null);
            jdbcUtils.close(conn,ps2,null);
        }
        return false;
    }

    @Override
    public List<Customer> queryCustomer(int start, int pageSize) {

        Connection conn = null;
        String sql = "select * from cst_customer limit ? offset ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Customer> list = new ArrayList<>();

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,pageSize);
            ps.setInt(2,pageSize * (start - 1));
            rs = ps.executeQuery();
            while (rs.next()) {
                int custId = rs.getInt(1);
                String custName = rs.getString(2);
                String custSource = rs.getString(3);
                String custIndustry = rs.getString(4);
                String custLevel = rs.getString(5);
                String custPhone = rs.getString(6);
                String custmoblie= rs.getString(7);
                list.add(new Customer(custId,custName,custSource,custIndustry,custLevel,custPhone,custmoblie));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public void updateCustomer(int custId,String custName, String custSource, String custIndustry,
                               String custLevel, String custPhone, String custMobile,
                               String custRegion, long custCapital, String custAddress,
                               String custMemo, String custFax, String custWebsite,
                               String custLicence, String custPic, String custCorporation,
                               String custBank) {

        Connection conn = null;
        String sql1="update cst_customer set cust_name='" + custName + "',cust_source='" + custSource + "',cust_industry='" + custIndustry + "',cust_level='" + custLevel + "',cust_phone='" + custPhone + "',cust_mobile='" + custMobile + "' where cust_id='" + custId + "'";
        String sql2="update cst_customer_detail set cust_region='" + custRegion + "',cust_capital='" + custCapital + "',cust_address='" + custAddress  + "',cust_fax='" + custFax + "',cust_website='" + custWebsite + "',cust_licence='" + custLicence + "',cust_pic='" + custPic + "',cust_corporation='" + custCorporation + "',cust_bank='" + custBank + "',cust_memo='" + custMemo + " 'where cust_id='" + custId + "'";
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql1);
            ps2 = conn.prepareStatement(sql2);
            ps.executeUpdate();
            ps2.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.close(conn,ps,null);
            jdbcUtils.close(conn,ps2,null);
        }
    }

    @Override
    public void deleteCustomer(int custId) {

        Connection conn = null;
        String sql = "delete from cst_customer where cust_id = ?";
        String sql2 = "delete from cst_customer_detail where cust_id=?";
        PreparedStatement ps = null;
        PreparedStatement ps2 = null;
        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,custId);
            ps.executeUpdate();
            ps2 = conn.prepareStatement(sql2);
            ps2.setInt(1,custId);
            ps2.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,null);
            jdbcUtils.close(conn,ps2,null);
        }

    }

    @Override
    public List<String> queryCustomerName() {

        String sql = "SELECT DISTINCT cust_id,cust_name from cst_customer";
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<String> list = new ArrayList();
        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()) {
                list.add(resultSet.getInt(1) + "-" + resultSet.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,resultSet);
        }
        return list;
    }

    @Override
    public int qureyCustomerIdByName(String name) {
        String sql = "select cust_id from cst_customer where cust_name = ?";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement ps = null;
        try {
            conn = jdbcUtils.getCon();
            ps= conn.prepareStatement(sql);
            ps.setString(1,name);
            rs = ps.executeQuery();

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<Customer> queryCustomByName(String name) {
        Connection conn = null;
        String sql = "select * from cst_customer where cust_name like ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Customer> list = new ArrayList<>();

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setString(1,'%' + name + '%');
            rs = ps.executeQuery();
            while (rs.next()) {
                int custId = rs.getInt(1);
                String custName = rs.getString(2);
                String custSource = rs.getString(3);
                String custIndustry = rs.getString(4);
                String custLevel = rs.getString(5);
                String custPhone = rs.getString(6);
                String custmoblie= rs.getString(7);
                list.add(new Customer(custId,custName,custSource,custIndustry,custLevel,custPhone,custmoblie));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return list;
    }

    @Override
    public Customer queryCustomById(int id) {
        Connection conn = null;
        String sql = "select * from cst_customer where cust_id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        Customer customer = null;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {
                int custId = rs.getInt(1);
                String custName = rs.getString(2);
                String custSource = rs.getString(3);
                String custIndustry = rs.getString(4);
                String custLevel = rs.getString(5);
                String custPhone = rs.getString(6);
                String custmoblie= rs.getString(7);
                customer = new Customer(custId,custName,custSource,custIndustry,custLevel,custPhone,custmoblie);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return customer;
    }

    @Override
    public CustomerDetail queryCustomDetailById(int id) {
        Connection conn = null;
        String sql = "select * from cst_customer_detail where cust_id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        CustomerDetail customerDetail = null;

        try {
            conn = jdbcUtils.getCon();
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            while (rs.next()) {

                int custId = rs.getInt(1);
                String custRegion = rs.getString(2);
                String custZip = rs.getString(3);
                String custAddress = rs.getString(4);
                String custFax = rs.getString(5);
                String custWebsite= rs.getString(6);
                String custLicence =rs.getString(7);
                String custCorporation =rs.getString(8);
                long custCapital = Integer.parseInt(rs.getString(9));
                String custBank =rs.getString(10);
                String custPic=rs.getString(11);
                String custMemo = rs.getString(12);
                customerDetail = new CustomerDetail(custId,custRegion,custZip,custAddress,custFax,custWebsite,custLicence,custCorporation,custCapital,custBank,custPic,custMemo);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            jdbcUtils.close(conn,ps,rs);
        }
        return customerDetail;
    }

}
